package no.ntnu.fp.storage.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import no.ntnu.fp.handlers.exceptions.DatabaseException;
import no.ntnu.fp.model.Appointment;
import no.ntnu.fp.model.Employee;
import no.ntnu.fp.model.MeetingRoom;
import no.ntnu.fp.server.ConnectionProvider;
import no.ntnu.fp.storage.AppointmentStorage;

public class AppointmentStorageImpl implements AppointmentStorage {
	private final EmployeeStorageImpl employeeStorageImpl;
	private final MeetingRoomStorageImpl meetingRoomStorageImpl;
	
	public AppointmentStorageImpl(EmployeeStorageImpl employeeStorageImpl, 
			MeetingRoomStorageImpl meetingRoomStorageImpl) {
		this.employeeStorageImpl = employeeStorageImpl;
		this.meetingRoomStorageImpl = meetingRoomStorageImpl;
	}

	@Override
	public List<Appointment> getAppointmentsByEmployee(Employee employee) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"SELECT * FROM Appointment WHERE employeeId = ?");
			stmt.setLong(1, employee.getId());
			
			ResultSet resultSet = stmt.executeQuery();
			
			List<Appointment> list = new ArrayList<Appointment>();
			while (resultSet.next()) {
				list.add(getCurrentAppointment(resultSet, connection, employee));
			}

			stmt.close();
			
			return list;
			
		} catch (SQLException ex) {
			throw new DatabaseException(
					"Unable to get appointments for employee " + employee, ex);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}


	@Override
	public Appointment save(Appointment appointment) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			connection.setAutoCommit(false);
			PreparedStatement stmt = connection.prepareStatement(
					"INSERT INTO Appointment(" +
					"appointmentId, employeeId, appointmentDate," +
					"startTime, endTime, description, " +
					"place, meetingRoomId, title) " +
					"VALUES(DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?)", 
					Statement.RETURN_GENERATED_KEYS);
			
			fillWithData(appointment, stmt);
			
			stmt.executeUpdate();
			
			ResultSet generatedKeys = stmt.getGeneratedKeys();
			
			if (generatedKeys.next()) {
				long appointmentId = generatedKeys.getLong(1);
				appointment = getAppointmentById(appointmentId, connection);
				stmt.close();
				return appointment;
				
			} else {
				stmt.close();
				throw new SQLException("No keys generated for appointment" +
						"with id = #" + appointment.getId());
			}
			
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new DatabaseException("Could not roll back erroneous" +
						"transaction " + e, e1);
			}
			throw new DatabaseException("Could not save appointment " +
				appointment + " for employee"  + appointment.getEmployee(), e);
		} finally {
			ConnectionProvider.relinquishConnection();
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				throw new DatabaseException("Could not revert autocommit" +
						" to true after transaction", e);
			}
		}
	}

	private void fillWithData(Appointment appointment, PreparedStatement stmt)
			throws SQLException {
		stmt.setLong(1, appointment.getEmployee().getId());
		stmt.setDate(2, appointment.getDate());
		stmt.setTime(3, appointment.getStartTime());
		stmt.setTime(4, appointment.getEndTime());
		stmt.setString(5, appointment.getDescription());
		stmt.setString(6, appointment.getPlace());
		
		MeetingRoom meetingRoom = appointment.getRoom();
		if (meetingRoom == null) {
			stmt.setNull(7, Types.INTEGER);
		} else {
			stmt.setLong(7, meetingRoom.getId());
		}
		
		stmt.setString(8, appointment.getTitle());
	}

	@Override
	public Appointment update(Appointment appointment) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			MeetingRoom meetingRoom = appointment.getRoom();
			String place = appointment.getPlace();

			//Clearing out place and meetingroomid so as not to get constraint
			//crashes when updating them separately, as explained below
			PreparedStatement stmt = connection.prepareStatement("UPDATE Appointment " +
										"SET place = NULL " +
										"WHERE appointmentId = ?");
			stmt.setLong(1, appointment.getId());
			stmt.executeUpdate();
			stmt.close();
			stmt = connection.prepareStatement("UPDATE Appointment " +
												"SET meetingRoomId = NULL " +
												"WHERE appointmentId = ?");
			stmt.setLong(1, appointment.getId());
			stmt.executeUpdate();
			stmt.close();
			
			//These two must be separated, because the room-place DB-constraint
			//and the on-update trigger sets off a derby bug which crashes the
			//operation with error message "Trying to insert string into int".
			//This could be because the place-meetingroomid comparison somehow
			//ends up converting the long-null into a string-null or something.
			updateTimeAndRoomInfo(appointment,	connection, meetingRoom);
			updateContentAndPlaceInfo(appointment, connection, place);
			
			return getAppointmentById(appointment.getId(), connection);
		} catch (SQLException e) {
			throw new DatabaseException("Could not update appointment " 
										+ appointment, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	private void updateContentAndPlaceInfo(Appointment appointment,
			Connection connection, String place) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"UPDATE Appointment " +
				"SET description = ?, " +
					"title = ?, " +
					"place = ? " +
					"WHERE appointmentId = ?");
		stmt.setString(1, appointment.getDescription());
		stmt.setString(2, appointment.getTitle());
		if (place == null) {
			stmt.setNull(3, Types.VARCHAR);
		} else {
			stmt.setString(3, place);
		}
		stmt.setLong(4, appointment.getId());
		stmt.executeUpdate();
		stmt.close();
	}

	private void updateTimeAndRoomInfo(Appointment appointment,
			Connection connection, MeetingRoom meetingRoom) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"UPDATE Appointment " +
				"SET meetingroomId = ?, " +
					"appointmentDate = ?, " +
					"startTime = ?, " +
					"endTime = ? " +
					"WHERE appointmentId = ?");
		
		if (meetingRoom == null) {
			stmt.setNull(1, Types.INTEGER);
		} else {
			stmt.setLong(1, meetingRoom.getId());
		}
		stmt.setDate(2, appointment.getDate());
		stmt.setTime(3, appointment.getStartTime());
		stmt.setTime(4, appointment.getEndTime());
		stmt.setLong(5, appointment.getId());
		stmt.executeUpdate();
		stmt.close();
	}
	
	Appointment getAppointmentById(long appointmentId, 
									Connection connection) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"SELECT * FROM Appointment " +
				"WHERE appointmentId = ?");
		stmt.setLong(1, appointmentId);
		
		ResultSet resultSet = stmt.executeQuery();
		
		if (resultSet.next()) {
			Appointment app = getCurrentAppointment(resultSet, connection, null);
			stmt.close();
			return app;
		} else {
			stmt.close();
			throw new SQLException("No appointment by id = #" + appointmentId);
		}
	}

	private Appointment getCurrentAppointment(ResultSet resultSet, 
			Connection connection, Employee employee) throws SQLException {
		long id = resultSet.getLong("appointmentId");
		String title = resultSet.getString("title");
		String description = resultSet.getString("description");
		String place = resultSet.getString("place");
		MeetingRoom meetingRoom = null;
		
		if (place == null) {
			long meetingRoomId = resultSet.getLong("meetingRoomId"); 
			if (meetingRoomId != 0) {
				meetingRoom = meetingRoomStorageImpl.getMeetingRoom(meetingRoomId, connection);
			}
		}
		
		if (employee == null) {
			long employeeId = resultSet.getLong("employeeId");
			employee = employeeStorageImpl.getEmployee(employeeId, connection);
		}
		
		Time startTime = resultSet.getTime("startTime");
		Time endTime = resultSet.getTime("endTime");
		Date appointmentDate = resultSet.getDate("appointmentDate");
		
		return new Appointment(id, place, title, description, meetingRoom,
				employee, startTime, endTime, appointmentDate);
	}

	@Override
	public void deleteAppointment(Appointment appointment) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"DELETE FROM Appointment " +
					"WHERE appointmentId = ?");
			stmt.setLong(1, appointment.getId());
			
			stmt.executeUpdate();
			
			stmt.close();
			
		} catch (SQLException e) {
			throw new DatabaseException("Could not delete appointment " + appointment, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}
}
